import pandas as pd
from selenium import webdriver
import time
import datetime

start = time.time()  # 记录时间
df = pd.read_excel('D:/查询内容/no_in.xlsx', dtype={'用电单元': str})
list_cha = []
list_name = []
list_res = []
# 创建电力用户号列表
for i in df['用电单元']:
    list_cha.append(i)
# 创建企业名称列表
for i in df['企业名称']:
    list_name.append(i)

url = 'https://220.194.186.220/'  # 电力交易平台网站
options = webdriver.ChromeOptions()  # 创建浏览器对象
options.add_argument('--ignore-certificate-errors')  # 设置此项可进入证书不对的
driver = webdriver.Chrome(options=options)
driver.get(url)
driver.find_element_by_xpath('//body/div/div/ul/li[@class="li04"]/a').click()
driver.find_element_by_xpath(
    "/html/body/div[@class='all']/div[@class='logo']/div[@class='login_all']/div[@class='login']/div[@class='login_center']/div[@class='login_center_data']/div[2]/div[@class='user_sel']/input[@id='loginname']").send_keys(
    "CZSHJGY")  # 输入账号
driver.find_element_by_xpath(
    "/html/body/div[@class='all']/div[@class='logo']/div[@class='login_all']/div[@class='login']/div[@class='login_center']/div[@class='login_center_data']/div[3]/div[@class='passw']/label[@id='loginpass1']").click()
driver.find_element_by_xpath(
    "/html/body/div[@class='all']/div[@class='logo']/div[@class='login_all']/div[@class='login']/div[@class='login_center']/div[@class='login_center_data']/div[3]/div[@class='passw_sel']/input[@id='loginpass']").send_keys(
    "g1t2r3+t4")
Y_name = input("输入验证码：")
driver.find_element_by_xpath(
    "/html/body/div[@class='all']/div[@class='logo']/div[@class='login_all']/div[@class='login']/div[@class='login_center']/div[@class='login_center_data']/table[@class='code']/tbody/tr/td[1]/input[@id='code']").send_keys(
    Y_name)
driver.find_element_by_xpath(
    "/html/body/div[@class='all']/div[@class='logo']/div[@class='login_all']/div[@class='login']/div[@class='login_center']/div[@class='login_center_button']/div[@id='lb']").click()
time.sleep(10)
driver.find_element_by_xpath(
    "/html/body/div[@class='all']/div[@class='index_menu']/ul[@id='menuLi']/li[2]/a").click()  # 点击基本信息
time.sleep(3)
driver.find_element_by_xpath(
    "/html/body/div[@class='all']/div[@class='index_data']/div[@id='menuDiv']/div[@id='leftMenuDiv0']/ul[@class='classul']/li[@class='classulli'][2]/a").click()  # 用电单元管理点击
time.sleep(3)
driver.switch_to.frame('contentFrame')  # document获取不到，属于子页面，需要转换
driver.find_element_by_xpath(
    "/html//div[@class='all']/div[@id='button']/h3/a[@id='editG1']").click()  # 点击查询营销计量点
driver.switch_to.default_content()  # 浏览器回退到父级目录
n = 0
n_cha = 0
n_NoIn = 0
n_in = 0
n_InNo=0
# 生成新的查询列表，只包含营销户号不存在的企业及电力用户号
list_chaNewName=[]
list_chaNewNum=[]
while n < len(list_cha):
    driver.switch_to.frame("jbox-iframe")  # 进入查询户号页面，也是一个document元素
    time.sleep(1)
    driver.find_element_by_xpath(
        "/html//div[@id='all']//input[@id='consno']").clear()
    driver.find_element_by_xpath(
        "/html//div[@id='all']//input[@id='consno']").send_keys(list_cha[n])  # 输入电力用户号
    driver.find_element_by_xpath(
        "/html//div[@id='all']//a[@id='saveBtn']").click()  # 点击查询

    driver.switch_to.default_content()  # 浏览器回退到父级目录
    
    
    
    #判断div的ID值
    if driver.find_element_by_xpath("/html/body/div[4]").get_attribute("id")=="DetailInfo2":
        text_res="在准入范围未绑定"
        list_res.append(text_res)
        driver.find_element_by_xpath("html/body/div[@id='DetailInfo2']/div[@id='jbox']/table/tbody/tr[2]/td[2]/div/a").click()
        print(list_name[n] + "," + list_cha[n] + ',' + text_res)
        n_InNo+=1
        n+=1
    else:
        
        text_res = driver.find_element_by_xpath(
            "/html/body/div/div[@class='messager-body panel-body panel-body-noborder window-body']/div[1]").text  # 查询结果文本
        print(list_name[n] + "," + list_cha[n] + ',' + text_res)
        list_res.append(text_res)
        time.sleep(1)
        driver.find_element_by_xpath(
            "/html/body/div/div[@class='messager-body panel-body panel-body-noborder window-body']//a").click()
        if text_res == "该营销户号不存在!":
            n_NoIn += 1
            list_chaNewName.append(list_name[n])
            list_chaNewNum.append(list_cha[n])
        else:
            n_in += 1
        n += 1
list_num = list(range(1, len(list_cha) + 1))
driver.quit()
df['查询结果'] = list_res
df.insert(0, '序号', list_num) #在最前列插入序号
r_time = datetime.datetime.now().strftime('%Y-%m-%d')
df.to_excel('准入范围企业查询(' + r_time + ').xlsx', index=False)
# 生成新的查询文件
df_new=pd.DataFrame({'企业名称':list_chaNewName,'用电单元':list_chaNewNum})
df_new.to_excel('D:/查询内容/no_in.xlsx',index=False)

end = time.time()
print("用时" + str(end - start) + "秒,共查询" + str(len(list_cha)) +
      "个电力用户号，其中不在出入范围的有" + str(n_NoIn) + "家,在准入范围已绑定的的有" + str(n_in) + "家，在准入范围未绑定的有"+str(n_InNo)+"家")
